
-- 创建数据库
create database xishuo;


-- 意见表
drop table if  exists `xs_advice`;
CREATE TABLE `xs_advice` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '姓名',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '邮箱',
  `advice` varchar(400) NOT NULL DEFAULT '' COMMENT '建议',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 评论表
drop table if  exists `xs_comment`;
CREATE TABLE `xs_comment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '评论表id',
  `comment_info` varchar(255) DEFAULT NULL COMMENT '评论内容',
  `comment_user` bigint(13) DEFAULT NULL COMMENT '评论人_userid',
  `comment_info_id` bigint(13) DEFAULT NULL COMMENT '被评论内容id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint(13) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint(13) DEFAULT NULL COMMENT '修改人',
  `del_falg` enum('0','1') DEFAULT '0' COMMENT '删除标识-通过删除标识来确定是否展示',
  `del_time` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 字典表
drop table if  exists `xs_dictionary`;
CREATE TABLE `xs_dictionary` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `type` bigint(11) NOT NULL DEFAULT '0' COMMENT '字典类别',
  `value` varchar(128) NOT NULL DEFAULT '' COMMENT '值',
  `del_falg` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0' COMMENT '删除标识',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8mb4;

-- 字典类型表
drop table if  exists `xs_dictionary_type`;
CREATE TABLE `xs_dictionary_type` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '' COMMENT '类别名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4;

-- 游戏类目表
drop table if  exists `xs_game_category`;
CREATE TABLE `xs_game_category` (
  `id` bigint(13) NOT NULL AUTO_INCREMENT COMMENT '游戏模块id',
  `game_name` varchar(255) NOT NULL DEFAULT '' COMMENT '游戏名称',
  `game_info` varchar(255) NOT NULL DEFAULT '' COMMENT '游戏介绍',
  `game_operators` varchar(255) NOT NULL DEFAULT '' COMMENT '游戏运营商',
  `game_image` bigint(13) NOT NULL DEFAULT '0' COMMENT '游戏图片id',
  `sort_id` int(11) NOT NULL DEFAULT '0' COMMENT '排列顺序id',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `create_user` bigint(13) NOT NULL DEFAULT '0' COMMENT '创建人',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_user` bigint(13) NOT NULL DEFAULT '0' COMMENT '修改人',
  `del_falg` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除标识',
  `del_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '删除时间',
  `del_user` bigint(13) NOT NULL DEFAULT '0' COMMENT '删除人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- 游戏详情表
drop table if  exists `xs_game_details`;
CREATE TABLE `xs_game_details` (
  `id` bigint(13) NOT NULL AUTO_INCREMENT COMMENT '游戏info_id',
  `game_id` bigint(13) NOT NULL DEFAULT '0' COMMENT '游戏类目id',
  `game_details` text NOT NULL COMMENT '游戏详细介绍',
  `game_dimage_id` bigint(13) NOT NULL DEFAULT '0' COMMENT '游戏详情图片id',
  `game_click` bigint(13) NOT NULL DEFAULT '0' COMMENT '文章点赞数',
  `game_follow` bigint(13) NOT NULL DEFAULT '0' COMMENT '文章关注数',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `create_user` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建人',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  `update_user` bigint(20) NOT NULL DEFAULT '0' COMMENT '修改人',
  `del_falg` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除标识',
  `del_time` datetime NOT NULL COMMENT '删除时间',
  `del_user` bigint(20) NOT NULL DEFAULT '0' COMMENT '删除人',
  `author` varchar(50) NOT NULL DEFAULT '' COMMENT '作者',
  `article_category` int(4) NOT NULL DEFAULT '0' COMMENT '文章类目',
  `title` varchar(50) NOT NULL DEFAULT '' COMMENT '文章标题',
  `introduction` varchar(500) NOT NULL DEFAULT '' COMMENT '文章简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 图片表
drop table if  exists `xs_img`;
CREATE TABLE `xs_img` (
  `id` bigint(13) NOT NULL AUTO_INCREMENT COMMENT '图片表id',
  `img_name` varchar(200) DEFAULT NULL COMMENT '图片名称',
  `img_url` varchar(255) DEFAULT NULL COMMENT '图片地址',
  `img_type` varchar(20) NOT NULL DEFAULT '' COMMENT '图片格式',
  `create_time` datetime DEFAULT NULL COMMENT '创建人',
  `create_user` bigint(13) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint(13) DEFAULT NULL COMMENT '修改人',
  `del_falg` enum('0','1') DEFAULT '0' COMMENT '删除标识',
  `del_time` datetime DEFAULT NULL COMMENT '删除时间',
  `del_user` bigint(13) DEFAULT NULL COMMENT '删除人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 菜单表
drop table if  exists `xs_menu`;
CREATE TABLE `xs_menu` (
  `MENU_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜单/按钮ID',
  `PARENT_ID` bigint(20) NOT NULL COMMENT '上级菜单ID',
  `MENU_NAME` varchar(50) NOT NULL COMMENT '菜单/按钮名称',
  `PATH` varchar(255) DEFAULT NULL COMMENT '对应路由path',
  `COMPONENT` varchar(255) DEFAULT NULL COMMENT '对应路由组件component',
  `PERMS` varchar(50) DEFAULT NULL COMMENT '权限标识',
  `ICON` varchar(50) DEFAULT NULL COMMENT '图标',
  `TYPE` char(2) NOT NULL COMMENT '类型 0菜单 1按钮',
  `ORDER_NUM` double(20,0) DEFAULT NULL COMMENT '排序',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint(13) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint(13) DEFAULT NULL COMMENT '修改人',
  `del_falg` enum('0','1') DEFAULT '0' COMMENT '删除标识',
  `del_time` datetime DEFAULT NULL COMMENT '删除时间',
  `localhost` int(2) DEFAULT '0' COMMENT '0后台1网页',
  PRIMARY KEY (`MENU_ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 角色表
drop table if  exists `xs_role`;
CREATE TABLE `xs_role` (
  `role_id` bigint(13) NOT NULL AUTO_INCREMENT COMMENT '角色id',
  `role_name` varchar(50) DEFAULT NULL COMMENT '角色名称',
  `remark` varchar(255) DEFAULT NULL COMMENT '角色描述',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint(13) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint(13) DEFAULT NULL COMMENT '修改人',
  `del_falg` enum('0','1') DEFAULT '0' COMMENT '删除标识',
  `del_time` datetime DEFAULT NULL COMMENT '删除使劲',
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- 角色菜单表
drop table if  exists `xs_role_menu`;
CREATE TABLE `xs_role_menu` (
  `ROLE_ID` bigint(20) NOT NULL,
  `MENU_ID` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 账号表
drop table if  exists `xs_advice`;
CREATE TABLE `xs_user` (
  `user_id` bigint(13) NOT NULL AUTO_INCREMENT COMMENT '用户表id',
  `user_name` varchar(50) NOT NULL COMMENT '用户名',
  `salt` varchar(10) DEFAULT '0' COMMENT '密码的盐',
  `pass_word` varchar(50) NOT NULL COMMENT '密码',
  `real_name` varchar(50) DEFAULT NULL COMMENT '昵称',
  `e_mail` varchar(50) DEFAULT NULL COMMENT '邮箱号',
  `phone_num` varchar(20) NOT NULL COMMENT '手机号',
  `sex` enum('0','1') DEFAULT NULL COMMENT '0-男,1-女',
  `click_num` bigint(20) DEFAULT NULL COMMENT '被赞数',
  `follow_num` bigint(20) DEFAULT NULL COMMENT '被关注数',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint(13) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint(20) DEFAULT NULL COMMENT '修改人',
  `del_falg` enum('0','1') DEFAULT '0' COMMENT '删除标识 0-未删除,1-已删除',
  `del_time` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- 账号配置表
drop table if  exists `xs_user_config`;
CREATE TABLE `xs_user_config` (
  `USER_ID` bigint(20) NOT NULL COMMENT '用户ID',
  `THEME` varchar(10) DEFAULT NULL COMMENT '系统主题 dark暗色风格，light明亮风格',
  `LAYOUT` varchar(10) DEFAULT NULL COMMENT '系统布局 side侧边栏，head顶部栏',
  `MULTI_PAGE` char(1) DEFAULT NULL COMMENT '页面风格 1多标签页 0单页',
  `FIX_SIDERBAR` char(1) DEFAULT NULL COMMENT '页面滚动是否固定侧边栏 1固定 0不固定',
  `FIX_HEADER` char(1) DEFAULT NULL COMMENT '页面滚动是否固定顶栏 1固定 0不固定',
  `COLOR` varchar(20) DEFAULT NULL COMMENT '主题颜色 RGB值',
  PRIMARY KEY (`USER_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 账号角色表
drop table if  exists `xs_user_role`;
CREATE TABLE `xs_user_role` (
  `user_id` bigint(13) NOT NULL COMMENT '用户ID',
  `role_id` bigint(13) DEFAULT NULL COMMENT '角色ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 导航导入数据sql
INSERT INTO `xs_menu` VALUES (140, 0, '系统管理', 'javascript:;', NULL, NULL, 'cog', '1', 1, '2019-08-05 11:34:13', 3, '2019-08-05 14:18:26', 3, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (141, 140, '导航管理', '/xsMenu/list', NULL, NULL, 'circle-o', '2', 1, '2019-08-05 11:51:06', 3, '2019-08-05 14:37:57', 3, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (142, 140, '角色管理', '/xsRole/list', NULL, NULL, 'circle-o', '2', 2, '2019-08-05 12:10:11', 3, NULL, NULL, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (143, 0, '游戏管理', 'javascript:;', NULL, NULL, 'gamepad', '1', 2, '2019-08-05 14:18:19', 3, NULL, NULL, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (144, 143, '游戏类目管理', '/xsGameGategory/list', NULL, NULL, 'circle-o', '2', 1, '2019-08-05 14:26:06', 3, NULL, NULL, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (145, 143, '游戏详情管理', '/xsGameDetails/list', NULL, NULL, 'circle-o', '2', 2, '2019-08-05 14:26:40', 3, '2019-08-05 14:37:42', 3, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (146, 140, '账号管理', '/xsUser/list', NULL, NULL, 'circle-o', '2', 3, '2019-08-05 23:09:00', 1, NULL, NULL, '0', NULL, 0);
INSERT INTO `xs_menu` VALUES (147, 0, '网站管理', 'javascript:;', NULL, NULL, 'circle-o', '1', 1, '2019-08-06 12:52:26', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (148, 147, '主页', '/sites/homePage', NULL, NULL, 'circle-o', '2', 1, '2019-08-06 12:53:23', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (149, 147, '论坛选择', '/sites/bbs_choose', NULL, NULL, 'circle-o', '2', 2, '2019-08-06 12:54:26', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (150, 147, '签约主播', '/sites/signingTheHost', NULL, NULL, 'circle-o', '2', 3, '2019-08-06 12:55:51', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (151, 147, '加入我们', '/sites/joinUs', NULL, NULL, 'circle-o', '2', 4, '2019-08-06 12:56:44', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (152, 147, '联系我们', '/sites/contactUs', NULL, NULL, 'circle-o', '2', 5, '2019-08-06 13:32:47', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (153, 147, '有奖征文', '/sites/prizeEssay', NULL, NULL, 'circle-o', '2', 6, '2019-08-06 13:33:49', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (154, 147, '关于我们', '/sites/aboutUs', NULL, NULL, 'circle-o', '2', 7, '2019-08-06 13:34:37', 3, NULL, NULL, '0', NULL, 1);
INSERT INTO `xs_menu` VALUES (155, 140, '字典管理', '/xsDictionary/list', NULL, NULL, 'circle-o', '2', 4, '2019-08-10 16:27:56', 1, NULL, NULL, '0', NULL, 0);

SET FOREIGN_KEY_CHECKS = 1;

--导入账号数据
BEGIN;
INSERT INTO `xs_user` VALUES ('1', '呼呼1', '530000', '3814150e22c62ca0cb90699f356710f4', null, null, '18226606738', null, null, null, '2019-08-02 23:10:03', null, '2019-08-05 23:22:50', '1', '0', null), ('2', 'huhuhu', '175723', '25fbf3ade0d585ffa82bec8fdacaf297', null, null, '17321346706', null, null, null, '2019-08-19 22:42:43', null, null, null, '0', null), ('3', 'admin', '752070', '766ba56129cefb26f09c30708de7c7b0', null, null, '18226606746', null, null, null, '2019-08-19 23:39:08', null, null, null, '0', null), ('4', 'aa', '578285', '60d29a5e1538a25d69c6156160e5d86f', null, null, '17321342706', null, null, null, '2019-08-20 23:33:02', null, null, null, '0', null);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

--导入角色数据
BEGIN;
INSERT INTO `xs_role` VALUES ('1', '管理员', '管理员', '2019-08-02 15:57:50', '0', '2019-08-02 15:57:55', '0', '0', '2019-08-02 15:58:01');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

--角色和账号关系数据
BEGIN;
INSERT INTO `xs_user_role` VALUES ('1', '1'), ('2', '1'), ('3', '1'), ('4', '1');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

--角色和菜单数据
BEGIN;
INSERT INTO `xs_role_menu` VALUES ('1', '140'), ('1', '141'), ('1', '142'), ('1', '146'), ('1', '155'), ('1', '143'), ('1', '144'), ('1', '145');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

--字典类型表
BEGIN;
INSERT INTO `xs_dictionary_type` VALUES ('40', '文章类目');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

--字典表
BEGIN;
INSERT INTO `xs_dictionary` VALUES ('136', '40', '武学区', '0'), ('137', '40', '装备区', '0'), ('138', '40', '副本区', '0'), ('139', '40', '任务区', '0');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

--文章类目表
DROP TABLE IF EXISTS `xs_article_category`;
CREATE TABLE `xs_article_category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '文章类目名称',
  `game_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '游戏类目id',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `del_falg` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除标识',
  `create_user` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建者',
  `update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_user` bigint(20) NOT NULL DEFAULT '0' COMMENT '修改者',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `xs_post`;
CREATE TABLE `xs_post` (
  `id` bigint(13) NOT NULL COMMENT '当前主键',
  `post_type` int(4) DEFAULT NULL COMMENT '帖子游戏类型',
  `post_title` varchar(255) DEFAULT NULL COMMENT '帖子标题',
  `post_content` text COMMENT '帖子内容',
  `parent_id` bigint(13) DEFAULT NULL COMMENT '父类id-跟帖的',
  `create_user` bigint(13) DEFAULT NULL COMMENT '帖子创建人',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `deleted` tinyint(1) DEFAULT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




